import sqlite3

from const import CONTRACT_NAME

from get_transaction import evaluate_trading, is_table_exist, pairing_transaction

import numpy as np

import pandas as pd


def update_data2db(account_num, conn):
    filename = "trading-list2.csv"
    df = pd.read_csv(filename)
    # 列名为中文，更改为英文
    df.columns = ["datetime", "code", "contract", "c_dt", "direction", "price",
                  "amount", "open", "transation", "fee", "t_b", "pnl", "num"]
    # 只取其中10列
    sql_df = df[["datetime", "code", "contract", "c_dt", "direction", "price",
                 "amount", "open", "transation", "fee"]]
    data_list = np.array(sql_df).tolist()
    sql_list = []
    for i in range(len(data_list) - 1):
        record = data_list[i]
        # print(record)
        dt_str = str(record[0])
        year = dt_str[0:4]
        month = dt_str[4:6]
        day = dt_str[6:8]
        new_dt_str = "%s-%s-%s 10:00:00" % (year, month, day)
        n_month = str(data_list[i + 1][0])[4:6]
        # print("year=%s, month=%s, day=%s" % (year, month, day))
        # print("n_month=%s" % n_month)
        if record[1] == "郑州":
            contract = "%s%s" % (CONTRACT_NAME[record[2]], str(record[3])[1:])
        else:
            contract = "%s%s" % (CONTRACT_NAME[record[2]], str(record[3]))
        table_name = "transaction_%s_%s_%s" % (account_num, year, month)
        # print(contract)
        t_serial = 0
        t_direction = 1 if record[4] == "买" else -1
        t_price = record[5]
        t_amount = record[6]
        t_open = 1 if record[7] == "开" else -1
        t_transaction = round(record[8], 2)
        t_fee = record[9]
        tuple_str = ("(datetime, contract, serial, direction, price, "
                     "amount, trans, open, fee)")
        tuple_value = (new_dt_str, contract, t_serial, t_direction, t_price,
                       t_amount, t_transaction, t_open, t_fee)
        sql_str = "INSERT INTO %s %s values %r" % (table_name, tuple_str,
                                                   tuple_value)
        print(sql_str)
        sql_list.append(sql_str)
        # 记录已到达下个月
        if n_month != month or i == len(data_list) - 2:
            conn.execute("DROP TABLE IF EXISTS %s" % table_name)
            print("重新建立表格 %s." % table_name)
            try:
                conn.execute("""CREATE TABLE %s
                    (id INTEGER PRIMARY KEY AUTOINCREMENT,
                    datetime TEXT NOT NULL,
                    contract TEXT NOT NULL,
                    serial INT NOT NULL,
                    direction INT NOT NULL,
                    price FLOAT NOT NULL,
                    amount INT NOT NULL,
                    trans FLOAT NOT NULL,
                    open INT NOT NULL,
                    fee FLOAT NOT NULL);""" % table_name)
            except sqlite3.OperationalError as error:
                print("操作Sqlite3数据库出现错误，错误信息是：%s" % error)
            for sql in sql_list:
                conn.execute(sql)
            conn.commit()
            sql_list = []


def main():
    account_num = "011002301945"
    conn = sqlite3.connect("data.sqlite")
    update_data2db(account_num, conn)
    months = ["0%s" % i for i in range(1, 5)]
    for month in months:
        table_name = "transaction_%s_2017_%s" % (account_num, month)
        if is_table_exist(conn, table_name) > 0:
            sql_str = ("SELECT datetime, contract, serial, direction, price, "
                       "amount, trans, open, fee FROM %s" % table_name)
            _list = conn.execute(sql_str).fetchall()
            print(_list[0])
            (pairing_list, dynamic_holding_list,
             waiting_list) = pairing_transaction(_list, conn, account_num,
                                                 "2017", month)
            evaluate_trading(pairing_list, 539998.39, 721804.18, 0, conn,
                             "2017", month, account_num)


if __name__ == '__main__':
        main()
